﻿Imports Microsoft.Office.Interop
Imports Capa_de_Acceso_a_Datos
Public Class ImportarTablas

#Region "Inicio y cierre del formulario"

    Private Sub ImportarTablas_FormClosed(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed
        frmMenuAuditoria.Show()
    End Sub

    Private Sub ImportarTablas_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        cargarComboAno()
        cargarComboMes()
        cargarComboObraSocialXPlan()
    End Sub

    Private Sub cargarComboAno()
        cboAno.DataSource = Constante.obtenerAnos
        cboAno.DisplayMember = "Ano"
        cboAno.ValueMember = "AnoId"
        cboAno.SelectedIndex = 0
    End Sub

    Private Sub cargarComboMes()
        cboMes.DataSource = MesDAO.obtenerMeses(0)
        cboMes.DisplayMember = "MesNombre"
        cboMes.ValueMember = "MesId"
        cboMes.SelectedValue = Today.Month
    End Sub

    Private Sub cargarComboObraSocialXPlan()
        cboObraSocial.ValueMember = "ObraSocialXPlanId"
        cboObraSocial.DisplayMember = "Descripcion"
        cboObraSocial.DataSource = ObraSocialXPlanDAO.comboObraSocialXPlan()
    End Sub

    Private Sub btnSalir_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSalir.Click
        frmMenuAuditoria.Show()
        Me.Close()
    End Sub

#End Region

#Region "Botones importar"

    'Private Function existeMedicamento(ByVal troquel As Integer, ByVal codigoBarra As String) As Integer

    '    'TODO el identificador de los medicamentos es el troquel Y el Codigo de barras


    '    'Si encontramos un sólo producto buscando por número de troquel devolvemos su id
    '    dt = MedicamentoDAO.buscarMedicamento(MedicamentoDAO.TROQUEL, troquel, Constante.VACIO, Constante.VACIO)
    '    If dt.Rows.Count = 1 Then
    '        Return dt.Rows(0)("MedicamentoId")
    '    End If

    '    'Si no encontramos sólo un medicamento con ese número de troquel buscamos por código de barras
    '    dt = MedicamentoDAO.buscarMedicamento(MedicamentoDAO.TROQUEL, Constante.MENOSUNO, codigoBarra, Constante.VACIO)
    '    If dt.Rows.Count = 1 Then
    '        Return dt.Rows(0)("MedicamentoId")
    '    End If

    '    'Si llegamos aquí quiere decir que no conseguimos un medicamento con el troquel o código de barras suministrado
    '    Return Constante.MENOSUNO
    'End Function

    Private Sub btnTamanoMedicamento_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Dim opf As New OpenFileDialog()
        opf.Filter = "Archivos Excel|*.xls"

        If opf.ShowDialog() <> Windows.Forms.DialogResult.OK Then
            Return
        End If
        Dim path As String = opf.FileName

        Dim app As New Excel.Application()
        Dim libro As Excel.Workbook
        Dim hoja As Excel.Worksheet
        libro = app.Workbooks.Open(path)
        hoja = libro.Worksheets(1)

        Dim f As Integer = 2
        Dim cId As Integer = 1
        Dim cNombre As Integer = 2

        'Insertamos el codigo OTROS con id = -1
        TamanoMedicamentoDAO.insertarTamanoMedicamento(-1, "Otros", "Tamaño de Medicamento sin especificar")

        While hoja.Cells(f, cNombre).ToString <> ""

            Dim vId As String = hoja.Cells(f, cId).value
            Dim vNombre As String = hoja.Cells(f, cNombre).value
            TamanoMedicamentoDAO.insertarTamanoMedicamento(vId, vNombre, "")

            f = f + 1
        End While

        app.Quit()
        GC.Collect()
    End Sub

    Private Sub btnMonodrogas_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Dim opf As New OpenFileDialog()
        opf.Filter = "Archivos Excel|*.xls"

        If opf.ShowDialog() <> Windows.Forms.DialogResult.OK Then
            Return
        End If
        Dim path As String = opf.FileName

        Dim app As New Excel.Application()
        Dim libro As Excel.Workbook
        Dim hoja As Excel.Worksheet
        libro = app.Workbooks.Open(path)
        hoja = libro.Worksheets(1)

        Dim f As Integer = 2
        Dim cId As Integer = 1
        Dim cNombre As Integer = 2

        'Insertamos el codigo OTROS con id = -1
        MonodrogaDAO.insertarMonodroga(-1, "Otros", "Tamaño de Medicamento sin especificar")

        While hoja.Cells(f, cNombre).ToString <> ""

            Dim vId As String = hoja.Cells(f, cId).value
            Dim vNombre As String = hoja.Cells(f, cNombre).value
            MonodrogaDAO.insertarMonodroga(vId, vNombre, "")

            f = f + 1
        End While

        app.Quit()
        GC.Collect()
    End Sub

    Private Sub btnMedicamentos_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMedicamentos.Click
        Dim opf As New OpenFileDialog()
        opf.Filter = "Archivos Excel|*.xls"

        If opf.ShowDialog() <> Windows.Forms.DialogResult.OK Then
            Return
        End If
        Dim path As String = opf.FileName

        Dim app As New Excel.Application()
        Dim libro As Excel.Workbook
        Dim hoja As Excel.Worksheet
        libro = app.Workbooks.Open(path)
        hoja = libro.Worksheets(1)

        Dim f As Integer = 2
        Dim cTroquel As Integer = 1
        Dim cNombre As Integer = 2
        Dim cPresentacion As Integer = 3
        Dim cImporte As Integer = 5
        Dim cTamano As Integer = 15
        Dim cAccionFarmacologica As Integer = 16
        Dim cMonodroga As Integer = 17
        Dim cCodigoBarra As Integer = 20
        Dim cLaboratorio As Integer = 24
        While hoja.Cells(f, cNombre).value <> ""
            Dim vTroquel As String = hoja.Cells(f, cTroquel).Value
            Dim vCodigoBarra As String = hoja.Cells(f, cCodigoBarra).value
            Dim vNombre As String = hoja.Cells(f, cNombre).value
            Dim vPresentacion As String = hoja.Cells(f, cPresentacion).value
            Dim vImporte As Double = CDbl(hoja.Cells(f, cImporte).value)
            Dim vTamano As String = hoja.Cells(f, cTamano).value
            IIf(Not IsNumeric(vTamano), vTamano = -1, vTamano = vTamano)

            Dim vAccionFarmacologica As String = hoja.Cells(f, cAccionFarmacologica).value
            IIf(Not IsNumeric(vAccionFarmacologica), vAccionFarmacologica = -1, vAccionFarmacologica = vAccionFarmacologica)

            Dim vMonodroga As Integer = hoja.Cells(f, cMonodroga).value()
            IIf(Not IsNumeric(vMonodroga), vMonodroga = -1, vMonodroga = vMonodroga)

            Dim vLaboratorio As Integer = hoja.Cells(f, cLaboratorio).value
            IIf(Not IsNumeric(vLaboratorio), vLaboratorio = -1, vLaboratorio = vLaboratorio)

            'Buscamos el id del medicamento
            Dim MedicamentoId As Integer = MedicamentoDAO.idMedicamento(vTroquel, vCodigoBarra)

            'Si no existe el medicamento cargado en la BD lo insertamos, si existe lo actualizamos
            If MedicamentoId = -1 Then
                MedicamentoDAO.insertarMedicamento(vTroquel, vCodigoBarra, vNombre, vPresentacion, vImporte, vLaboratorio, vTamano, 0, vMonodroga)
            Else
                MedicamentoDAO.actualizarMedicamento(MedicamentoId, vTroquel, vCodigoBarra, vNombre, vPresentacion, vImporte, vLaboratorio, vTamano, 0, vMonodroga)
            End If

            f = f + 1
        End While

        app.Quit()
        GC.Collect()
    End Sub

    Private Sub btnLaboratorios_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLaboratorios.Click
        Dim opf As New OpenFileDialog()
        opf.Filter = "Archivos Excel|*.xls"

        If opf.ShowDialog() <> Windows.Forms.DialogResult.OK Then
            Return
        End If
        Dim path As String = opf.FileName

        Dim app As New Excel.Application()
        Dim libro As Excel.Workbook
        Dim hoja As Excel.Worksheet
        libro = app.Workbooks.Open(path)
        hoja = libro.Worksheets(1)

        Dim f As Integer = 2
        Dim cId As Integer = 1
        Dim cNombre As Integer = 2

        'Insertamos el codigo OTROS con id = -1
        LaboratorioDAO.insertarLaboratorio(-1, "Otros", "", "Laboratorio sin especificar")

        While hoja.Cells(f, cNombre).ToString <> ""

            Dim vId As String = hoja.Cells(f, cId).value
            Dim vNombre As String = hoja.Cells(f, cNombre).value

            LaboratorioDAO.insertarLaboratorio(vId, vNombre, "", "")
            f = f + 1
        End While

        app.Quit()
        GC.Collect()
    End Sub

    Private Sub btnAccionFarmacologica_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAccionFarmacologica.Click
        Dim opf As New OpenFileDialog()
        opf.Filter = "Archivos Excel|*.xls"

        If opf.ShowDialog() <> Windows.Forms.DialogResult.OK Then
            Return
        End If
        Dim path As String = opf.FileName

        Dim app As New Excel.Application()
        Dim libro As Excel.Workbook
        Dim hoja As Excel.Worksheet
        libro = app.Workbooks.Open(path)
        hoja = libro.Worksheets(1)

        Dim f As Integer = 2
        Dim cId As Integer = 1
        Dim cNombre As Integer = 2

        'Insertamos el codigo OTROS con id = -1
        AccionFarmacologicaDAO.insertarAccionFarmacologica(-1, "Otros", "Accion Farmacológica sin especificar")

        While hoja.Cells(f, cNombre).ToString <> ""

            Dim vId As String = hoja.Cells(f, cId).value
            Dim vNombre As String = hoja.Cells(f, cNombre).value
            AccionFarmacologicaDAO.insertarAccionFarmacologica(vId, vNombre, "")

            f = f + 1
        End While

        app.Quit()
        GC.Collect()
    End Sub

    Private Sub btnVademecum_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnVademecum.Click
        Dim vademecumId As Integer = VademecumDAO.idVademecum(cboAno.SelectedValue, cboMes.SelectedValue, cboObraSocial.SelectedValue)
        'Verificamos si existe el vademecum
        If vademecumId > 0 Then
            MsgBox("Ya exite un vademecum para la Obra Social seleccionada en el periodo determinado", MsgBoxStyle.OkOnly, "Ya existe vademecum")
            Exit Sub
        End If

        Dim opf As New OpenFileDialog()
        opf.Filter = "Archivos Excel|*.xls"

        If opf.ShowDialog() <> Windows.Forms.DialogResult.OK Then
            Exit Sub
        End If
        Dim path As String = opf.FileName

        Dim app As New Excel.Application()
        Dim libro As Excel.Workbook
        Dim hoja As Excel.Worksheet
        libro = app.Workbooks.Open(path)
        hoja = libro.Worksheets(1)

        Dim f As Integer = 2
        Dim cTroquel As Integer = 1
        Dim cPorcentaje As Integer = 8

        'Creamos el vademecum
        VademecumDAO.insertarVademecum(cboObraSocial.SelectedValue, cboMes.SelectedValue, cboAno.SelectedValue)

        'Actualizamos el vademecumId
        vademecumId = VademecumDAO.idVademecum(cboAno.SelectedValue, cboMes.SelectedValue, cboObraSocial.SelectedValue)

        While hoja.Cells(f, cTroquel).value <> ""


            Dim vTroquel As String = hoja.Cells(f, cTroquel).value
            Dim vPorcentaje As String = hoja.Cells(f, cPorcentaje).value
            'Reemplazamos los '.' por ','
            vPorcentaje = vPorcentaje.Replace(".", ",")
            IIf(Not IsNumeric(vPorcentaje), vPorcentaje = 0, vPorcentaje = vPorcentaje)

            'Buscamos el id del medicamento por su troquel
            Dim dt As DataTable = MedicamentoDAO.buscarMedicamento(MedicamentoDAO.TROQUEL, vTroquel, "", "")

            'si está el medicamento cargado en la base de datos
            If dt.Rows.Count > 0 Then
                Dim vMedicamentoId As Integer = dt.Rows(0)("MedicamentoId")
                DetalleDetalleVademecumDAO.insertarDetalleVademecum(vademecumId, vMedicamentoId, vPorcentaje)
            End If

            f = f + 1
        End While

        app.Quit()
        GC.Collect()
    End Sub

#End Region

End Class
